﻿using System;
using System.Collections.Generic;
using System.ComponentModel;

using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace BenhVien
{
    public partial class FormHSCTThuocTungBN : Form
    {
        SqlConnection con;
        private System.Data.DataTable dtThuoc = new DataTable("tblBNBNNGT");
        private SqlDataAdapter daThuoc = new SqlDataAdapter();

        private void Connect()
        {
            try
            {
                con = new SqlConnection("Data Source=192.168.1.200,1433;Server=IWANTWIN-PC;user id=sa;password=123456;database=QLBenhVien; Connection Timeout=15");
                con.Open();
            }
            catch
            {
                MessageBox.Show("Ket noi co so du lieu that bai!", "Loi", MessageBoxButtons.OK);
            }

        }
        private void getData()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = @" select * from BenhNhan,Thuoc,CapThuocBenhNhan where BenhNhan.MaBN = CapThuocBenhNhan.MaBN and CapThuocBenhNhan.MaThuoc = Thuoc.MaThuoc";
            daThuoc.SelectCommand = command;
            daThuoc.Fill(dtThuoc);
            //Chu y cai nay

            if (dtThuoc.Rows.Count > 0)
            {
                grvThuocTungBN.DataSource = dtThuoc;
            }
            else
            {
                MessageBox.Show("Khong tim thay du lieu dac biet", "Loi", MessageBoxButtons.OK);
            }
        }

        private void binding()
        {
            textMaBN.DataBindings.Clear();
            textMaBN.DataBindings.Add("Text", grvThuocTungBN.DataSource, "MaBN");

            textHoBN.DataBindings.Clear();
            textHoBN.DataBindings.Add("Text", grvThuocTungBN.DataSource, "Ho");

            textTenBN.DataBindings.Clear();
            textTenBN.DataBindings.Add("Text", grvThuocTungBN.DataSource, "Ten");

            textDiaChi.DataBindings.Clear();
            textDiaChi.DataBindings.Add("Text", grvThuocTungBN.DataSource, "DiaChi");

            textTenThuoc.DataBindings.Clear();
            textTenThuoc.DataBindings.Add("Text", grvThuocTungBN.DataSource, "TenThuoc");

            textMoTa.DataBindings.Clear();
            textMoTa.DataBindings.Add("Text", grvThuocTungBN.DataSource, "MoTa");

            textLieuLuong.DataBindings.Clear();
            textLieuLuong.DataBindings.Add("Text", grvThuocTungBN.DataSource, "LieuLuong");

            textCachDung.DataBindings.Clear();
            textCachDung.DataBindings.Add("Text", grvThuocTungBN.DataSource, "CachDung");

            textSLTrongNgay.DataBindings.Clear();
            textSLTrongNgay.DataBindings.Add("Text", grvThuocTungBN.DataSource, "SoLuongTrongNgay");

            textNgayBD.DataBindings.Clear();
            textNgayBD.DataBindings.Add("Text", grvThuocTungBN.DataSource, "NgayBatDau");

            textNgayKT.DataBindings.Clear();
            textNgayKT.DataBindings.Add("Text", grvThuocTungBN.DataSource, "NgayKetThuc");



        }
        private void disconnect()
        {
            con.Close();

        }
        public FormHSCTThuocTungBN()
        {
            InitializeComponent();
        }

        private void FormHSCTThuocTungBN_Load(object sender, EventArgs e)
        {
            Connect();
            getData();
            if (dtThuoc.Rows.Count > 0) binding();
            disconnect();
        }

        private void groupBox2_Enter(object sender, EventArgs e)
        {

        }

        private void textBox5_TextChanged(object sender, EventArgs e)
        {

        }

        private void btInDSNV_Click(object sender, EventArgs e)
        {
            Excel.Application app = new Excel.Application();
            Excel.Workbook wb = app.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
            Excel._Worksheet ws = (Excel._Worksheet)app.ActiveSheet;

            ws.Name = "Thong tin chi tiet";
            app.Visible = true;

            ws.Cells[1, 1] = "MaBN";
            ws.Cells[1, 2] = "Ho BN";
            ws.Cells[1, 3] = "Ten BN";
            ws.Cells[1, 4] = "Dia Chi";
            ws.Cells[1, 5] = "Ten Thuoc";

            ws.Cells[1, 6] = "Mo Ta";
            ws.Cells[1, 7] = "Lieu Luong ";
            ws.Cells[1, 8] = "Cach dung";
            ws.Cells[1, 9] = "So luong trong ngay";
            ws.Cells[1, 10] = "Ngay Bat dau";

            ws.Cells[1, 11] = "Ngay ket thuc";


            ws.Cells[2, 1] = textMaBN.Text;
            ws.Cells[2, 2] = textHoBN.Text;
            ws.Cells[2, 3] = textTenBN.Text;
            ws.Cells[2, 4] = textDiaChi.Text;
            ws.Cells[2, 5] = textTenThuoc.Text;

            ws.Cells[2, 6] = textMoTa.Text;
            ws.Cells[2, 7] = textLieuLuong.Text;
            ws.Cells[2, 8] = textCachDung.Text;
            ws.Cells[2, 9] = textSLTrongNgay.Text;
            ws.Cells[2, 10] = textNgayBD.Text;

            ws.Cells[2, 11] = textNgayKT.Text;

        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btTimKiem_Click(object sender, EventArgs e)
        {
            dtThuoc.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = @"select * from BenhNhan,Thuoc,CapThuocBenhNhan where BenhNhan.MaBN = CapThuocBenhNhan.MaBN and CapThuocBenhNhan.MaThuoc = Thuoc.MaThuoc and Ho LIKE '%'+@HoTK+'%' AND Ten LIKE '%'+@TenTK+'%' ";

            command.Parameters.Add("@HoTK", SqlDbType.NVarChar, 50).Value = textTKHoBN.Text;
            command.Parameters.Add("@TenTK", SqlDbType.NVarChar, 50).Value = textTKTenBN.Text;
            daThuoc.SelectCommand = command;
            daThuoc.Fill(dtThuoc);
            if (dtThuoc.Rows.Count > 0)
            {

                grvThuocTungBN.DataSource = dtThuoc;
            }
            else
            {
                MessageBox.Show("Khong co du lieu", "Loi", MessageBoxButtons.OK);
            }
        }

        private void btXoa_Click(object sender, EventArgs e)
        {
            textTKHoBN.Clear();
            textTKTenBN.Clear();
        }
    }
}
